﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace SqlServerDriver
{
	/*
		Class to help split up the implementation of the SQL server driver
		This handles the nitty gritty of assembling the SQL and marking up the DataTable
	*/
	class TableCommandProcessor
	{
		internal TableCommandProcessor(SqlServerDataSource parent)
		{
			this.dataSource = parent;
		}

		/*
			Convert the view definition into SQL. This for the table data
		*/
		internal String TableDataCommandString(DbView.ViewContext context, String filterClause)
		{
			// getuseful stuff from the context
			DbView.ViewDefinition vp = context.ViewDef;
			String tableName = this.TableNameFromContext(context);
			// delegate to original version
			return this.TableDataCommandString(tableName, vp, filterClause);
		}

		/*
			Called directly to generate a sql string for updates.
		*/
		internal String TableDataCommandString(String tableName, DbView.ViewDefinition vp, String filterClause)
		{
			int top = 500; // prepare for parameterization
			// note: only add nolock when there is an existing sort ket
			// because experimenation showed that adding this can upset a "natural"
			// key sort order that was being relied on.
			// Actually the observation above requires more investigation. 
			// Hence we're going with it for a while
			String nolockDflt = " with (nolock)";
			nolockDflt = "";
			// on initial display of data table there won't be a view definition yet
			if (vp == null)
				// bad sql, but it saves a separate query for fields.
				return String.Format("select top {1} * from {0}{2}", Helper.SafeTableName(tableName), top, nolockDflt);

			// from here building Sql based on the current view definition.
			DbView.SchemaField[] display = vp.DisplayFields;
			String fieldList = "";
			foreach (DbView.SchemaField f in display)
			{
				if (fieldList.Length > 0) fieldList += ",";
				fieldList += f.SqlSafeName;
			}
			DbView.SchemaField[] sort = vp.SortFields;
			String sortClause = "";
			String nolockClause = nolockDflt; // maybe, maybe not
			foreach (DbView.SchemaField f in sort)
			{
				if (sortClause.Length > 0) sortClause += ",";
				sortClause += f.SqlSafeName;
				if (f.Ord == "D")
					sortClause += " DESC";
			}
			if (sortClause.Length > 0)
			{
				sortClause = " ORDER BY " + sortClause;
				if (!Helper.TableIsSubQuery(tableName)) // can't have nolock with subqueries
					nolockClause = " with (nolock)";
			}

			String whereClause = "";

			// filter text search 
			if (filterClause.Length > 0)
			{
				// numeric test
				bool isNumeric = this.IsNumericSearch(filterClause);
				// filter clause logic
				DbView.SchemaField[] search = vp.SearchFields;
				if (isNumeric)
				{
					foreach (DbView.SchemaField f in search)
					{
						if (DbView.SchemaField.IsIntegerType(f.Type))
						{
							if (whereClause.Length > 0) whereClause += " or ";
							whereClause += String.Format("{0} = {1}", f.SqlSafeName, filterClause);
						}
					}
				}
				else
				{
					foreach (DbView.SchemaField f in search)
					{
						if (!DbView.SchemaField.IsIntegerType(f.Type))
						{
							if (whereClause.Length > 0) whereClause += " or ";
							// create a special form clause that can be modified to provide case-insensitive searching
							// in a case-sensitive database
							String clause = String.Format("[[UPPER({0}) like '%%']]", f.SqlSafeName);
							clause = SqlServerTemplates.Templates.ProcessFilterClause(clause, filterClause, this.dataSource.IsCaseSensitive);
							whereClause += clause;
							// WhereClause += String.Format("{0} like '%{1}%'", f.Name, FilterClause);
						}
					}
				}
				if (whereClause.Length > 0) whereClause = String.Format("({0})", whereClause);
			}
			// view definition extra search clause
			if (vp.ExtraClause.Length > 0)
			{
				if (whereClause.Length > 0) whereClause += " and ";
				whereClause += "(" + vp.ExtraClause + ")";
			}

			// additional ad-hoc filter clause
			if (vp.FilterClause.Length > 0)
			{
				if (whereClause.Length > 0) whereClause += " and ";
				whereClause += "(" + vp.FilterClause + ")";
			}

			if (whereClause.Length > 0) whereClause = " where " + whereClause;
			String sqlTemplate = @"
select top {4} {1} 
  from {0}{5}
  {2}
  {3}
";
			String sql = string.Format(sqlTemplate, Helper.SafeTableName(tableName), fieldList, whereClause, sortClause, top, nolockClause);
			return sql;
		}

		internal String MakeFieldsCommandString(DbView.ViewContext context, String sqlTemplate, String filterString)
		{
			// grab the table from the view context
			String tableName = this.TableNameFromContext(context);
			// assemble actual SQL from the template, table name and filter parameter.
			return AddFilterClause(String.Format(sqlTemplate, tableName), filterString);
		}

		// just need the table
		internal String MakeFieldDescriptionsCommandString(String tableName, String sqlTemplate, String filterString)
		{
			String command = String.Format(sqlTemplate, tableName);
			return AddFilterClause(command, filterString);
		}

		internal String MakePermissionsCommand(String commandTemplate, DbView.Command command, String filterString)
		{
			String paramValue = (command["SUMMARY"] == "Y") ? "1" : "0";  // convert from Y/N to SQL where clause value
			return AddFilterClause(String.Format(commandTemplate, paramValue), filterString);
		}

		internal String MakeArticlesCommand(String commandTemplate, DbView.Command command, String filterString)
		{
			String extraClause = ""; // if from the main menu
			String objectName = command[DbView.Command.TableParameterName(0)];
			if (command["PUB"] == "Y")
			{
				// see {1} in ARTICLES_SQLTEMPLATE
				extraClause = String.Format("and (upper(p.name) = '{0}' or upper(p.name) = '{0}')", objectName);
			}
			String newTemplate = AddFilterClause(commandTemplate, filterString);
			return String.Format(newTemplate, extraClause);
		}

		internal String AggregateDataCommandString(String commandTemplate, DbView.ViewContext context)
		{
			DbView.ViewDefinition viewDef = context.ViewDef;
			DbView.SchemaField field = viewDef.SchemaFields[0];
			int top = 1000;
			String fieldName = field.Name;
			String tableName = viewDef.TableName;
			String nullField = this.MakeNullProxy(field);
			String filterClause = this.MakeFilterClause(context);
			String commandString = String.Format(commandTemplate, fieldName, tableName, context.FilterString.ToUpper(),
												top, nullField, filterClause);
			// optimisation: remove the line
			if (String.IsNullOrEmpty(context.FilterString))
				commandString = Helper.RemoveTaggedLine(commandString, "-- Row Filter");
			return commandString;
		}

		internal String RowCountCommandString(String commandTemplate, DbView.ViewContext context)
		{
			DbView.ViewDefinition viewDef = context.ViewDef;
			DbView.SchemaField field = viewDef.SchemaFields[0];
			String fieldName = field.Name;
			String tableName = viewDef.TableName;
			String filterClause = this.MakeFilterClause(context); // later.
			return String.Format(commandTemplate, fieldName, tableName, context.FilterString.ToUpper(),
														filterClause);
		}

		internal String MakeDependenciesCommand(String commandTemplate, DbView.Command command, String filterString)
		{
			String extraClause = ""; // if from the main menu
			String objectName = command[DbView.Command.TableParameterName(0)];
			if (objectName.Length > 0)
			{
				// see {1} in DEPENDENCY_SQLTEMPLATE
				if (this.dataSource.SqlVersion <= 2000) // HACK
					extraClause = String.Format("and (upper(o2.name) = '{0}' or upper(o.name) = '{0}')", objectName);
				else
					extraClause = String.Format("and (o2.object_id = object_id('{0}') or o.object_id = object_id('{0}'))", objectName);
			}
			return String.Format(commandTemplate, filterString, extraClause);
		}

		//////////////////////////////////////////////////////////////////////////////////////////////
		// The data-fetch part. This is called from the bakground thread kicked off by 
		// Mainpresenter.PopulateGrid() for any command that returns a table
		// 
		public DataTable BuildDataTable(DbView.Command command, String commandString, DbView.ViewContext context)
		{
			String tableName = this.TableNameFromContext(context);
			String connectionString = this.dataSource.ConnectionString;
			/* Hacky override for table sizes to force the name of the DataTable to "Tables"
			 * This because of another hacky function;MainPresenter.GetTableName() that
			 * uses a variety of means to infer a true database table name for functions
			 * such as field lists and data etc. One strategy is to test the DataTable name
			 * for the text "Tables" and use the column 1 value. Perhaps better would be to 
			 * decorate the DataTable with extra info.
			*/
			if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.TableSizes)
				tableName = DbView.Commands.Cmnd.Tables.ToString();

			DataSet dataset = new DataSet();

			try
			{
				// New for 2012. QuickDiff has already built the table
				if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.DisplayHeldTable)
				{
					int tableId = this.GetHeldTableId(command);
					dataset.Tables.Add(DataTableRepository.Instance.GetTable(tableId));
				}
				else
					// Overwhelmingly normal branch is here where a SQL command is issued to the database.
					Helper.GenericMakeDset(connectionString, commandString, tableName, out dataset);
			}
			catch (Exception exc)
			{
				/*
					Allowing for the case where the connection does not have permission to access the row count
					stats. In this case fallback to a query that just returns table names.
				*/
				if (command.Cmd == DbView.Commands.Cmnd.Tables)
					commandString = this.TryAltTables(tableName, context.FilterString, out dataset);
				else
					// Counting Nulls fails for some types. Rather than fix this rare situation I'm
					// disabling the feature and using an alternative simpler SQL statement that just reports non-nulls.
					if (command.Cmd == DbView.Commands.Cmnd.Aggregate)
					{
						if (this.IsXmlTypeError(exc.Message))
							throw new Exception("Cannot perform aggregation on Xml");
						else
							commandString = this.TryAltAggregate(tableName, command, context.FilterString, out dataset);
					}
					else
						// Hacked test for Xml searching, which throws an error in original form
						if (command.Cmd == DbView.Commands.Cmnd.TableData && this.IsXmlTypeError(exc.Message))
							commandString = this.TryAltXmlSearch(tableName, commandString, out dataset);
						else
							throw;  // rethrow an unanticipated SQL error (timeout, syntax error etc.
			}
			// and ad-hoc command did not generate a result set. Rather than encounter problems later 
			// attach an empty one to the result Dset. This will allow normal empty row processing later.
			if (command.Cmd == DbView.Commands.Cmnd.Adhoc && dataset.Tables.Count == 0)
			{
				dataset.Tables.Add();
			}
			//////////////////////////////////////////////////////////////////////////////////////////////////////
			// Following data=retrieval we can now use DataTable properties to mark-up the table in a way that the
			// display grid can recognise. Hence we can colour columns. rows and cells to make our point clearer.
			//
			DataTable table = dataset.Tables[0];
			// [Specialise: markup columns to be added as pop-up command parameters ]
			DbView.DataTableExtraInfoManager mgr = new DbView.DataTableExtraInfoManager(table);
			mgr.Command = commandString;
			//
			if (command.Cmd == DbView.Commands.Cmnd.TableData)
			{
				// decorate. This adds the coloured columns for PKey, FKey and Indexes.
				this.AddColumnMetaData(mgr);
			}
			// below flags up other columns required as parameters for some pop-ups.
			if (command.Cmd == DbView.Commands.Cmnd.ForeignKeys)
			{
				mgr.SetParam(table.Columns[4], CmdParam.FkName);
				mgr.SetParam(table.Columns[0], CmdParam.FkTable);
				mgr.SetParam(table.Columns[1], CmdParam.FkField);
				mgr.SetParam(table.Columns[2], CmdParam.PkTable);
				mgr.SetParam(table.Columns[3], CmdParam.PkField);
			}
			if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.Descriptions)
			{
				mgr.SetParam(table.Columns[1], "DESC");
			}
			if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.ExtendedProperties)
			{
				mgr.SetParam(table.Columns[1], "PROP");
			}
			if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.PropertyValues)
			{
				// Get help from the official class
				// all this is for scripting and editing
				mgr.SetParam(table.Columns[ExtendedPropertiesTemplates.COLUMN_PROPERTY_NAME], "PROP");
				mgr.SetParam(table.Columns[ExtendedPropertiesTemplates.COLUMN_PROPERTY_VALUE], "PROPVAL");
				DataColumn dc = table.Columns[ExtendedPropertiesTemplates.COLUMN_TABLE_COLUMN];
				if (dc != null)
					mgr.SetParam(dc, "COL");
			}
			if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.Jobs)
			{
				mgr.SetParam(table.Columns[2], "JOBID");
			}
			if (command.Cmd == DbView.Commands.Cmnd.Dependencies)
			{
				mgr.SetParam(table.Columns[2], "HIGHLIGHT"); // flags the dependent object column for highlight in source
			}
			if (command.Cmd == DbView.Commands.Cmnd.Extension && command.Name == Cmd.Owners)
			{
				mgr.SetParam(table.Columns[1], "TYPE");
			}
			if (command.Cmd == DbView.Commands.Cmnd.AllSourceSearch)
			{
				table = this.DoObjectSearchTable(dataset, command);
			}


			if (SqlServerBrokerPopupBuilder.IsBrokerCommand(command))
				SqlServerBrokerPopupBuilder.CommandParameters(command, mgr);
			return table;
		}

		private bool IsXmlTypeError(string exceptionMessage)
		{
			// Searching error "The Xml data type ..." 
			// aggregate error "Data type Xml cannot ..."
			string seek1 = "DATA TYPE";
			string seek2 = "XML";

			return (exceptionMessage.ToUpper().IndexOf(seek1) != -1 && exceptionMessage.ToUpper().IndexOf(seek2) != -1);
		}

		private String TryAltAggregate(string tableName, DbView.Command command, string filter, out DataSet dataset)
		{
			String connectionString = this.dataSource.ConnectionString;
			int top = 1000;
			String template = SqlServerTemplates.AltAggregateTemplate(this.dataSource.SqlVersion);
			template = SqlServerTemplates.Templates.ProcessFilterClause(template, filter, this.dataSource.IsCaseSensitive);
			String field = command["FIELD"];
			String commandString = String.Format(template, field, tableName, filter, top);
			Helper.GenericMakeDset(connectionString, commandString, tableName, out dataset);
			return commandString; // return this so that it can be hooked onto the data table
		}

		private String TryAltTables(String tableName, String filter, out DataSet dataset)
		{
			String connectionString = this.dataSource.ConnectionString;
			String template = SqlServerTemplates.AltTableTemplate(this.dataSource.SqlVersion);
			template = SqlServerTemplates.Templates.ProcessFilterClause(template, filter, this.dataSource.IsCaseSensitive);
			String commandString = String.Format(template, filter);
			Helper.GenericMakeDset(connectionString, commandString, tableName, out dataset);
			return commandString; // return this so that it can be hooked onto the data table
		}

		// TODO: multiples
		// tidy up
		private String TryAltXmlSearch(String tableName, String originalSql, out DataSet dataset)
		{
			String connectionString = this.dataSource.ConnectionString;
			// fix originalSql here.
			originalSql = ModifyCommand(originalSql);
			Helper.GenericMakeDset(connectionString, originalSql, tableName, out dataset);
			return originalSql; // return this so that it can be hooked onto the data table
		}

		// look for [field] like '% and replace with convert(nvarchar(MAX), []) 
		private static String ModifyCommand(String originalSql)
		{
			SimpleRegex reg = new SimpleRegex(@"\[.*?\]\s+like\s+'%");
			int[,] hits = reg.GetMatches(originalSql);
			// process backwards so as not to disturb the indexes
			int item = hits.GetLength(0);
			while (item-- > 0) // test first the decrement. 
			{
				int changeAnchor = hits[item, 0];  // convenience
				// find end of field
				int pos = originalSql.IndexOf(']', changeAnchor);
				// carve up sql and replace
				originalSql = originalSql.Substring(0, changeAnchor) +  // before the field
							  "convert(nvarchar(max), " + originalSql.Substring(changeAnchor, pos - changeAnchor + 1) + ")" +  // convert Xml to nvarchar (will fail with non-ascii if varchar)
							  originalSql.Substring(pos + 1);          // remainder
			}
			return originalSql;
		}

		int GetHeldTableId(DbView.Command command)
		{
			String strId = command[CmdParam.TableId];
			int id = 0;
			if (!int.TryParse(strId, out id))
				throw new Exception("Failed to find table data");
			return id;
		}

		void AddColumnMetaData(DbView.DataTableExtraInfoManager mgr)
		{
			String connectionString = this.dataSource.ConnectionString;
			// need a query
			String commandString = this.dataSource.GetSchemaSql(mgr.Table.TableName);
			DataSet dataset = new DataSet();
			Helper.GenericMakeDset(connectionString, commandString, mgr.Table.TableName, out dataset);
			DataTable table = dataset.Tables[0];

			// check for pkeys
			int pkeyCount = 0;
			foreach (DataRow row in table.Rows)
				if (row["PKey?"].ToString().Length > 2) ++pkeyCount;
			bool modifiable = (pkeyCount > 0); // can't modify a table w/o a pkey
			// mark up pkeys, indexes and fkeys for colouring
			foreach (DataColumn col in mgr.Table.Columns)
			{
				String search = String.Format("[Field Name]='{0}'", col.ColumnName);
				DataRow[] row = table.Select(search);
				if (row.GetLength(0) > 0)
				{
					if (row[0]["PKey?"].ToString().Length > 2)
					{
						mgr.SetPkey(col);
						--pkeyCount; // counting down to 0
					}
					if (row[0]["FKey?"].ToString().Length > 2)
						mgr.SetFkey(col);
					if (row[0]["Index?"].ToString().Length > 2)
						mgr.SetIndexed(col);
				}
				if (modifiable && pkeyCount == 0) // not all pkeys are in the view
					mgr.SetModifiable();
			}
		}

		///////////////////////////////////////////////////////////////////////
		// Helpers
		// used by parent 
		internal String AddFilterClause(String command, String filterText)
		{
			// delegate to template processor
			if (HasModifiedWhereClause(command))
				return SqlServerTemplates.Templates.ProcessFilterClause(command, filterText, this.dataSource.IsCaseSensitive);
			return String.Format(command, filterText); // what about Case sensitive.
		}

		static internal String AddFilterClause(String command, String filterText, bool isCaseSensitive)
		{
			// delegate to template processor
			if (HasModifiedWhereClause(command))
				return SqlServerTemplates.Templates.ProcessFilterClause(command, filterText, isCaseSensitive);
			return String.Format(command, filterText); // what about Case sensitive.
		}

		// Detecting templates where the only configuration is the filter string
		internal bool SimpleTemplate(String commandTemplate)
		{
			if (commandTemplate.Length == 0) return false; // don't know what this condition is about - bad!
			// for fancy filters the filterstring is not added by string format
			if (HasModifiedWhereClause(commandTemplate))
			{
				if (commandTemplate.IndexOf("{0}") == -1)
					return true;
			}
			else
				// {0} is for the filter string therefore if there is no {1} this is simple
				if (commandTemplate.IndexOf("{1}") == -1)
					return true;
			return false;
		}

		// Templates include special mark-up this knows that [[ ]] encloses a where clause that has a variety of flavours. See below
		static bool HasModifiedWhereClause(String commandTemplate) { return (commandTemplate.IndexOf("[[") > 0); }

		String TableNameFromContext(DbView.ViewContext context)
		{
			// if there is a non-null view definition the table is 
			// there.
			if (context.ViewDef != null) return context.ViewDef.TableName;
			// otherwise this is (hopefully) a popup from the table list
			else return context.ObjectName;
		}

		bool IsNumericSearch(String filter)
		{
			Int64 bigint;
			return Int64.TryParse(filter, out bigint);
		}

		private string MakeFilterClause(DbView.ViewContext context)
		{
			// add the Filter clause.
			DbView.ViewDefinition vp = context.ViewDef;
			String extraClause = vp.ExtraClause;
			if (extraClause.Length > 0 && vp.FilterClause.Length > 0)
				extraClause += " AND ";
			if (vp.FilterClause.Length > 0)
				extraClause += vp.FilterClause;
			if (extraClause.Length == 0) return "";
			return " AND " + extraClause;
		}

		private String MakeNullProxy(DbView.SchemaField field)
		{
			return (field.Type == "S") ? "`" : // choose an unlikely character.
			(field.Type == "I") ? "-8989" :  // random very unusual value.
			(field.Type == "J") ? "-89898989" :  // random very unusual value (bigint)
			(field.Type == "N") ? "-8989.9898" :  // random very unusual value.
			(field.Type == "X") ? "239" :     // will be inaccurate, but no choice
			(field.Type == "D") ? "19700102"
							   : "0"; // this should suit all
		}

		// New from pre-search of source for tables or columns make anexact search
		// test results against RedGate product
		// Tidy up source form
		//  1. get whole word turned on by default or show whole words if any match
		//  2. get next and prev and match count taking account of option.
		private DataTable DoObjectSearchTable(DataSet dataset, DbView.Command command)
		{
			// extract the search from the command
			String tgt = command["SEARCH"].ToUpper(); // extract the search from the command
			// build an appropriate Regex
			String before = @"(^|[\s|'|.|!|""|%|&|(|*|+|,|\-|||:|;|<|=|>|\^|[])";    // keep this in sync with the version in SourceForm2.cs
			String after = @"([\s|'|.|!|""|%|&|)|*|+|,|\-|||:|;|<|=|>|\^|\]]|$)"; // keep this in sync with the version in SourceForm2.cs
			String pattern = before + tgt + after;
			SimpleRegex search = new SimpleRegex(pattern);
			// Input
			DataTable dt = dataset.Tables[0];
			// output filtered by first true match only
			DataTable dt2 = Helper.MakeDataTable(dt.TableName, "ObjectName|Type|Source", "SSS");
			String lastObject = "";  // hold last object matched to prevent duplicate hits in large source
			foreach (DataRow dr in dt.Rows)
			{
				String objectName = dr[0].ToString();
				if (lastObject != objectName)
				{
					String sourceCode = dr[2].ToString();
					String objectType = dr[1].ToString();
					String matchLine = this.FirstMatch(sourceCode, search);
					if (matchLine.Length > 0) // match if non-zero length string returned.
					{
						dt2.Rows.Add(new object[] { objectName, objectType, matchLine });
						lastObject = objectName;
					}
				}
			}
			return dt2;
		}

		private string FirstMatch(string sourceCode, SimpleRegex search)
		{
			String[] lines = sourceCode.Split('\n');
			foreach (String line in lines)
			{
				if (search.IsMatch(line.ToUpper()))
					return line;
			}
			return "";
		}

		SqlServerDataSource dataSource;
	}
}
